﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace UniversityStudentManagemnetSystem.DAO.DAL.Gateway
{
    public class DesignationGateway:DAO.Gateway.Gateway
    {
        public bool DesignationSave(Designation designation)
        {
            try
            {
                Connection.Open();
                string insertQuery = string.Format("insert into t_Designation values (@name)");
                Command.CommandText = insertQuery;
                Command.Parameters.Clear();
                Command.Parameters.Add("@name", SqlDbType.VarChar);
                Command.Parameters["@name"].Value = designation.DesignationName;
                Command.ExecuteNonQuery();
            }
            catch (Exception exception)
            {
                throw new Exception("Save couldn't performed due to system error!", exception);
            }
            finally
            {
                if (Connection!=null&&Connection.State==ConnectionState.Open)
                {
                    Connection.Close();
                }
            }
            return true;
        }
        public List<Designation> GetAllDesignation()
        {
            Connection.Open();
            string selectQuery = string.Format("select *from t_Designation");
            Command.CommandText = selectQuery;
            List<Designation> designations= new List<Designation>();
            SqlDataReader reader = Command.ExecuteReader();
            while (reader.Read())
            {
                Designation aDesignation = new Designation();
                aDesignation.DesignationId = Convert.ToInt32(reader["id"]);
                aDesignation.DesignationName = reader["name"].ToString();
                designations.Add(aDesignation);
            }
            reader.Close();
            Connection.Close();
            return designations;
        }

        public bool HasThisDesignationName(string designationName)
        {
            bool hasRows = false;
            try
            {
                Connection.Open();
                string query = String.Format("SELECT * FROM t_Designation WHERE Name=@name");
                Command.CommandText = query;
                Command.Parameters.Clear();
                Command.Parameters.Add("@name", SqlDbType.VarChar);
                Command.Parameters["@name"].Value = designationName;
                SqlDataReader reader = Command.ExecuteReader();
             

                if (reader != null)
                {

                    hasRows = reader.HasRows;
                }
            }
            catch (FormatException exception)
            {
                throw new Exception("Input was not in Correct Type!", exception);
            }
            catch (Exception exception)
            {

                throw new Exception("Sorry!System Error!Please Try Again! ", exception);
            }
            finally
            {
                if (Connection != null && Connection.State == ConnectionState.Open)
                {
                    Connection.Close();
                }
            }

            
            return hasRows;
        }

        public DataTable GetAllEmployeeDesignations()
        {
            try
            {

                string selectQuery = string.Format("select * from t_EmployeeDesignation");
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(selectQuery, GetConnectionString());
                DataSet myDataset = new DataSet();
                sqlDataAdapter.Fill(myDataset, "EmployeeDesignationTable");
                DataTable myDataTable = myDataset.Tables["EmployeeDesignationTable"];
                return myDataTable;
            }
            catch (Exception exception)
            {
                
                throw new Exception(exception.Message);
            }
            
        }

        public DataTable GetAllOfficerDesignations()
        {
            try
            {

                string selectQuery = string.Format("select * from t_OfficerDesignation");
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(selectQuery, GetConnectionString());
                DataSet dataSet = new DataSet();
                sqlDataAdapter.Fill(dataSet, "OfficerDesignationTable");
                DataTable dataTable = dataSet.Tables["OfficerDesignationTable"];
                return dataTable;
            }
            catch (Exception exception)
            {

                throw new Exception(exception.Message);
            }
            
        }
    }
}